﻿using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using OMG.Fill.Frunit.Model.Ent;
using OMG.Fill.Frunit.Service;
using OMG.Fill.Frunit.Services;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Web;
using System.Web.Http;
namespace OMG.Fill.Frunit.Model.UI.Controllers
{
    public class FruProSeasonController : ApiController
    {
        FruProSeasonServices fruProSeasonServices = new FruProSeasonServices();
        
        //[ApiActionAuthorize("获取主要水果生产情况季度统计表分页列表对象操作")]
        //[ApiActionException]
        public Pager<List<FruProSeasonEntity>> PostPageList(FruProSeasonQuery query)
        {
            return fruProSeasonServices.GetPageList(query);
        }
        //[ApiActionAuthorize("新增主要水果生产情况季度统计表指定对象操作")]
        //[ApiActionException]
        //public ResResult<int> Post(List<FruProSeasonEntity> list) {
        //    return fruProSeasonServices.AddInfo(list);
        //}
        //[ApiActionAuthorize("修改主要水果生产情况季度统计表指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Put(List<FruProSeasonEntity> list)
        {
            return fruProSeasonServices.UpdateInfo(list);
        }
        //[ApiActionAuthorize("删除主要水果生产情况季度统计表指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Delete(string id)
        {
            return fruProSeasonServices.DeleteInfo(id);
        }
        //[ApiActionAuthorize("获取主要水果生产情况季度统计表指定对象操作")]
        //[ApiActionException]
        //public ResResult<FruProSeasonEntity> Get(string id)
        //{
        //    return fruProSeasonServices.GetModel(id);
        //}

        public ResResult<List<FruProSeasonEntity>> PostImportData(string ReportId)
        {
            ReportServices reportServices = new ReportServices();
            ResResult<List<FruProSeasonEntity>> obj = new ResResult<List<FruProSeasonEntity>>();
            //ResResult<int> SingleObj = new ResResult<int>();
            HttpContext curContext = HttpContext.Current;
            HttpFileCollection files = curContext.Request.Files;
            if (curContext.Request.Files.Count != 0)
            {
                HttpPostedFile upfile = files[0];
                if (upfile != null && upfile.ContentLength > 0)
                {
                    string temppath = curContext.Server.MapPath("~/App_Data/temppath");
                    if (!Directory.Exists(temppath))//判断文件夹是否存在 
                    {
                        Directory.CreateDirectory(temppath);//不存在则创建文件夹 
                    }
                    string filetype = Path.GetExtension(upfile.FileName);
                    string filenewName = Guid.NewGuid().ToString().Replace("-", "") + filetype;
                    string strpath = Path.Combine(temppath, filenewName);
                    upfile.SaveAs(strpath);
                    FruProSeasonServices fruProSeasonServices = new FruProSeasonServices();
                    List<FruProSeasonEntity> ProSeasonList = fruProSeasonServices.GetModel(ReportId).model;
                    List<FruProSeasonEntity> excelList = fruProSeasonServices.ExcelToModel(strpath, filetype, "", 4);
                    List<FruProSeasonEntity> templateList = fruProSeasonServices.GetTabContent(ReportId);//获取到模板excel,用于跟导入的数据比较，看类型位置是否发生改动
                    FruProSeasonEntity pentity = excelList[2];//柑橘项
                    pentity.ZyArea = 0;
                    pentity.YshArea = 0;
                    pentity.YshYield = 0;
                    pentity.AddArea = 0;
                    for (int k = 3; k <= 8; k++)
                    {
                        if (excelList[k].ZyArea != null)
                            pentity.ZyArea += excelList[k].ZyArea;
                        if (excelList[k].YshArea != null)
                            pentity.YshArea += excelList[k].YshArea;
                        if (excelList[k].YshYield != null)
                            pentity.YshYield += excelList[k].YshYield;
                        if (excelList[k].AddArea != null)
                            pentity.AddArea += excelList[k].AddArea;
                    }
                    if(excelList[2].ZyArea < pentity.ZyArea || excelList[2].YshArea < pentity.YshArea || excelList[2].YshYield < pentity.YshYield ||
                        excelList[2].AddArea < pentity.AddArea)
                    {
                        obj.message = "您的导入文件柑橘项小于其子项之和！";
                        obj.status = DefaultKey.ErrorResult;
                        return obj;
                    }
                    for (int i = 0; i < templateList.Count; i++)
                    {
                        if (excelList[i].Variety != templateList[i].Variety)
                        {
                            obj.message = "您的导入文件已发生改变，无法正常导入数据，请重新下载模板。";
                            obj.status = DefaultKey.ErrorResult;
                            return obj;
                        }
                    }
                    for (int i = 0; i < ProSeasonList.Count; i++)
                    {
                        ProSeasonList[i].ZyArea = excelList[i].ZyArea;
                        ProSeasonList[i].YshArea = excelList[i].YshArea;
                        ProSeasonList[i].YshYield = excelList[i].YshYield;
                        ProSeasonList[i].AvgPrice = excelList[i].AvgPrice;
                        ProSeasonList[i].AddArea = excelList[i].AddArea;
                        ProSeasonList[i].Remark = excelList[i].Remark;
                    }
                    fruProSeasonServices.UpdateInfo(ProSeasonList);
                    reportServices.UpStatus(ReportId, "2", "");
                    obj.model = ProSeasonList;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "数据导入成功！！";
                    Directory.Delete(temppath, true);
                }
            }
            return obj;
        }

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream Method(String ReportId)
        {
            FileStream file = null;
            MemoryStream ms = new MemoryStream();
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\FruDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("FruProSeason") as HSSFSheet;//读取模板所在sheet位置
            List<FruProSeasonEntity> pagelist = fruProSeasonServices.GetModel(ReportId).model;//数据获取
            try
            {
                if (sheet != null)
                {
                    HSSFRow row = (HSSFRow)sheet.GetRow(4);//读取模板第5行
                    var style = row.GetCell(1).CellStyle;//读取模板第二行第一单元格样式并保存
                    row.GetCell(1).CellStyle = style;//对第二行第一单元格设置原来样式
                    int startRow = 4;
                    //获取有数据的行数
                    int rowCount = 0;
                    for (int i = startRow; i <= sheet.LastRowNum; i++)
                    {
                        IRow irow = sheet.GetRow(i);
                        if (irow != null)
                        {
                            rowCount = sheet.GetRow(i).RowNum;
                        }
                        else
                        {
                            break;
                        }
                    }
                    int j = 0;             
                    for (int i = startRow; i <= rowCount; i++)
                    {                       
                        HSSFRow datarow = (HSSFRow)sheet.GetRow(i);                       
                        if (datarow.GetCell(1) != null)
                        {
                            datarow.GetCell(1).SetCellValue(pagelist[j].ZyArea == null ? "" : string.Format("{0:N1}", pagelist[j].ZyArea));
                        }
                        else
                        {
                            datarow.CreateCell(1).SetCellValue(pagelist[j].ZyArea == null ? "" : string.Format("{0:N1}", pagelist[j].ZyArea));
                        }
                        if (datarow.GetCell(2) != null)
                        {
                            datarow.GetCell(2).SetCellValue(pagelist[j].YshArea == null ? "" : string.Format("{0:N1}", pagelist[j].YshArea));
                        }
                        else
                        {
                            datarow.CreateCell(2).SetCellValue(pagelist[j].YshArea == null ? "" : string.Format("{0:N1}", pagelist[j].YshArea));
                        }
                        if (datarow.GetCell(3) != null)
                        {
                            datarow.GetCell(3).SetCellValue(pagelist[j].YshYield == null ? "" : string.Format("{0:N1}", pagelist[j].YshYield));
                        }
                        else
                        {
                            datarow.CreateCell(3).SetCellValue(pagelist[j].YshYield == null ? "" : string.Format("{0:N1}", pagelist[j].YshYield));
                        }
                        if (datarow.GetCell(4) != null)
                        {
                            datarow.GetCell(4).SetCellValue(pagelist[j].AvgPrice == null ? "" : string.Format("{0:N2}", pagelist[j].AvgPrice));
                        }
                        else
                        {
                            datarow.CreateCell(4).SetCellValue(pagelist[j].AvgPrice == null ? "" : string.Format("{0:N2}", pagelist[j].AvgPrice));
                        }
                        if (datarow.GetCell(5) != null)
                        {
                            datarow.GetCell(5).SetCellValue(pagelist[j].AddArea == null ? "" : string.Format("{0:N1}", pagelist[j].AddArea));
                        }
                        else
                        {
                            datarow.CreateCell(5).SetCellValue(pagelist[j].AddArea == null ? "" : string.Format("{0:N1}", pagelist[j].AddArea));
                        }
                        if (datarow.GetCell(6) != null)
                        {
                            datarow.GetCell(6).SetCellValue(pagelist[j].Remark == null ? "" :  pagelist[j].Remark);
                        }
                        else
                        {
                            datarow.CreateCell(6).SetCellValue(pagelist[j].Remark == null ? "" :  pagelist[j].Remark);
                        }
                        datarow.GetCell(1).CellStyle = style;
                        datarow.GetCell(2).CellStyle = style;
                        datarow.GetCell(3).CellStyle = style;
                        datarow.GetCell(4).CellStyle = style;
                        datarow.GetCell(5).CellStyle = style;
                        datarow.GetCell(6).CellStyle = style;
                        j++;
                    }                                                               
                        sheet.CopyTo(workbook, "Sheet1", true, true);
                        workbook.Write(ms);
                        ms.Flush();
                        ms.Position = 0;
                   
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.ToString().Trim());
            }
            return ms;
        }

        /// <summary>
        /// 导出下载响应
        /// </summary>
        /// <param name="ReportCode"></param>
        public void GetExcelData(string ReportId,string FillInYear,string TimesCycle,string AreaName)
        {
            
            //打开完成excel文件并下载
            HttpContext curContext = HttpContext.Current;
            // 设置编码和附件格式
            curContext.Response.AddHeader("Access-Control-Allow-Origin", "*");
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            //curContext.Response.Charset = "GB2312";//防止中文出现乱码
            //System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);//防止中文出现乱码
            //curContext.Response.ContentEncoding = Encoding.GetEncoding("GB2312");  //设置输出流为简体中文 
            string strFileName = "（一）"+FillInYear+"年-第"+TimesCycle+"-"+AreaName + "-主要水果生产情况季度统计表.xls";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
            //curContext.Response.AppendHeader("Content-Disposition",
             //   "attachment;filename=" + strFileName);
            curContext.Response.BinaryWrite(Method(ReportId).GetBuffer());
            curContext.Response.End();
        }

        /// <summary>
        /// 获取主要水果品种字典
        /// </summary>
        /// <returns></returns>
        public ResResult<List<FruProSeasonEntity>> GetVarietyDic()
        {
            ResResult<List<FruProSeasonEntity>> obj = new ResResult<List<FruProSeasonEntity>>();
            List<FruProSeasonEntity> list = new List<FruProSeasonEntity>();
            List<FruProSeasonEntity> templist = fruProSeasonServices.GetTabContent("");//此时合计位于最后一行
            //int len = templist.Count;
            //FruProSeasonEntity totalmodel = templist[len - 1];
            //totalmodel.Variety = "全部";
            //templist.RemoveAt(len - 1);
            //list.Add(totalmodel);
            //list.AddRange(templist);
            obj.model = templist;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "";
            return obj;
        }
        /// <summary>
        /// 获取统计的列表数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<ProSeasonStatisModel>> GetStatisListData(string AreaCode, string FillInYear, string VarietyCode,string TimesCycle)
        {
            if (string.IsNullOrEmpty(VarietyCode)){
                ResResult<List<ProSeasonStatisModel>> obj = new ResResult<List<ProSeasonStatisModel>>();
                List<ProSeasonStatisModel> list = new List<ProSeasonStatisModel>();
                List<FruProSeasonEntity> varietyList = fruProSeasonServices.GetTabContent("");
                for (int i = 0; i < varietyList.Count; i++)
                {
                    string code = varietyList[i].VarietyCode;
                    ProSeasonStatisModel model = new ProSeasonStatisModel();
                    ProSeasonStatisModel tempmodel;
                    if (AreaCode.Equals("530000000000"))
                    {
                        tempmodel = fruProSeasonServices.getTProvinStatis(FillInYear, code, TimesCycle);
                    }
                    else if(AreaCode.Substring(4, 8).Equals("00000000"))
                    {
                        tempmodel = fruProSeasonServices.getProvinStatis(AreaCode,FillInYear, code, TimesCycle);
                    }
                    else
                    {
                        tempmodel = fruProSeasonServices.getCountyStatisList(AreaCode, FillInYear, code, TimesCycle)[0];
                    }
                    if (tempmodel != null)
                    {
                        model = tempmodel;
                    }
                    model.ParentCode = varietyList[i].ParentCode;
                    model.Variety = varietyList[i].Variety;
                    model.VarietyCode = code;           
                    list.Add(model);                    
                }
                obj.model = list;
                obj.status = DefaultKey.SuccessResult;
                obj.message = "获取成功！";
                return obj;
            }
            else{
                return fruProSeasonServices.getStatisListData(AreaCode, FillInYear, VarietyCode, TimesCycle);
            }
            
        }

        public void GetStatisExcel(string AreaCode, string FillInYear, string TimesCycle, string DownType)
        {
            AreaServices areaServices = new AreaServices();
            string AreaName = areaServices.GetByCodeModel(AreaCode).model.Name;
            //打开完成excel文件并下载
            HttpContext curContext = HttpContext.Current;
            // 设置编码和附件格式
            curContext.Response.AddHeader("Access-Control-Allow-Origin", "*");
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            string strFileName = FillInYear + "年-第" + TimesCycle + "-" + AreaName + "-生产情况汇总表.xls";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
            //curContext.Response.AppendHeader("Content-Disposition",
            //   "attachment;filename=" + strFileName);
            curContext.Response.BinaryWrite(loadStatis(AreaCode, FillInYear, TimesCycle,DownType).GetBuffer());
            curContext.Response.End();
        }

        public MemoryStream loadStatis(string AreaCode, string FillInYear, string TimesCycle,string DownType)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\FruStatisDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet tsheet = templateBook.GetSheet("FruProSeason1") as HSSFSheet;//读取模板所在sheet位置
            if (DownType.Equals("1"))
            {
                List<ProSeasonStatisModel> totallist = GetStatisListData(AreaCode, FillInYear, null, TimesCycle).model;//数据获取

                HSSFRow row = (HSSFRow)tsheet.GetRow(3);//读取模板第4行
                var style = row.GetCell(1).CellStyle;//读取模板第4行第2单元格样式并保存
                row.GetCell(1).CellStyle = style;//对第3行第2单元格设置原来样式
                HSSFSheet sheet = CopyToSheet(tsheet, totallist,DownType);               
                AreaServices areaServices = new AreaServices();
                string areaName = areaServices.GetByCodeModel(AreaCode).model.Name;
                sheet.CopyTo(workbook, areaName, true, true);
                List<ProSeasonStatisModel> childlist = new List<ProSeasonStatisModel>();
                if (AreaCode.Equals("530000000000"))
                {
                    var citys = areaServices.GetPrefectureList();
                    foreach (string areacode in citys)
                    {
                        childlist = (List<ProSeasonStatisModel>)GetStatisListData(areacode, FillInYear, null, TimesCycle).model;
                        FileStream fm = new FileStream(templateName, FileMode.Open, FileAccess.Read);
                        HSSFWorkbook Book = new HSSFWorkbook(fm);
                        HSSFSheet temp = Book.GetSheet("FruProSeason1") as HSSFSheet;
                        HSSFSheet sheet1 = CopyToSheet(temp, childlist,DownType);
                        string areaName1 = areaServices.GetByCodeModel(areacode).model.Name;
                        sheet1.CopyTo(workbook, areaName1, true, true);
                    }
                    //新增县级合计总表
                    List<ProSeasonStatisModel> sumlist = fruProSeasonServices.getOutStatisData(AreaCode, FillInYear, "sum", TimesCycle).model;
                    HSSFSheet vsheet = templateBook.GetSheet("FruProSeason") as HSSFSheet;
                    vsheet = CopyToSheet(vsheet, sumlist, "2");
                    vsheet.CopyTo(workbook, "合计", true, true);
                }
                else if (AreaCode.Substring(4, 8).Equals("00000000"))
                {
                    var countys = areaServices.GetChildList(AreaCode);
                    foreach (string areacode in countys)
                    {
                        childlist = (List<ProSeasonStatisModel>)fruProSeasonServices.getCountyStatisList(areacode, FillInYear, null, TimesCycle);
                        FileStream fm = new FileStream(templateName, FileMode.Open, FileAccess.Read);
                        HSSFWorkbook Book = new HSSFWorkbook(fm);
                        HSSFSheet temp = Book.GetSheet("FruProSeason1") as HSSFSheet;
                        HSSFSheet sheet1 = CopyToSheet(temp, childlist, DownType);
                        string areaName1 = areaServices.GetByCodeModel(areacode).model.Name;
                        sheet1.CopyTo(workbook, areaName1, true, true);
                    }
                }
            }

            else
            {
                List<FruProSeasonEntity> varietyList = fruProSeasonServices.GetTabContent("");
                /* HSSFSheet sheet1 = (HSSFSheet)workbook.CreateSheet("在园面积");
                 HSSFSheet sheet2 = (HSSFSheet)workbook.CreateSheet("已收获面积");
                 HSSFSheet sheet3 = (HSSFSheet)workbook.CreateSheet("已收获产量");
                 HSSFSheet sheet4 = (HSSFSheet)workbook.CreateSheet("平均地头批发价");
                 HSSFSheet sheet5 = (HSSFSheet)workbook.CreateSheet("季度新增面积");
                 HSSFRow vrow1 = (HSSFRow)sheet1.CreateRow(3);
                 HSSFRow vrow2 = (HSSFRow)sheet2.CreateRow(3);
                 HSSFRow vrow3 = (HSSFRow)sheet3.CreateRow(3);
                 HSSFRow vrow4 = (HSSFRow)sheet4.CreateRow(3);
                 HSSFRow vrow5 = (HSSFRow)sheet5.CreateRow(3);
                 if (!AreaCode.Equals("530000000000"))
                 {
                     workbook.Remove(sheet1);
                     workbook.Remove(sheet2);
                     workbook.Remove(sheet3);
                     workbook.Remove(sheet4);
                     workbook.Remove(sheet5);
                 }*/
                for (int i = 0; i < varietyList.Count; i++)
                {
                    HSSFSheet vsheet = templateBook.GetSheet("FruProSeason") as HSSFSheet;

                    HSSFRow row = (HSSFRow)vsheet.GetRow(3);//读取模板第4行
                    var style = row.GetCell(1).CellStyle;//读取模板第4行第2单元格样式并保存
                    row.GetCell(1).CellStyle = style;//对第3行第2单元格设置原来样式

                    string varietyCode = varietyList[i].VarietyCode;
                    List<ProSeasonStatisModel> citylist = fruProSeasonServices.getOutStatisData(AreaCode, FillInYear, varietyCode, TimesCycle).model;
                    vsheet = CopyToSheet(vsheet, citylist, DownType);                    
                    vsheet.CopyTo(workbook, varietyList[i].Variety, true, true);
                    /* if (AreaCode.Equals("530000000000"))
                     {                                
                         int ss = i + 1;
                         vrow1.CreateCell(i+1).SetCellValue(varietyList[i].Variety);                                      
                         vrow2.CreateCell(i + 1).SetCellValue(varietyList[i].Variety);                   
                         vrow3.CreateCell(i + 1).SetCellValue(varietyList[i].Variety);                  
                         vrow4.CreateCell(i + 1).SetCellValue(varietyList[i].Variety);                  
                         vrow5.CreateCell(i + 1).SetCellValue(varietyList[i].Variety);                                                                             
                       List<ProSeasonStatisModel> countylist = fruProSeasonServices.getAllCountyStatisList( FillInYear, varietyCode, TimesCycle);
                       for(int t = 0; t < countylist.Count; t++)
                        {
                             if (i == 0)
                             {
                                 HSSFRow drow1 = (HSSFRow)sheet1.CreateRow(4 + t);
                                 drow1.CreateCell(0).SetCellValue(countylist[t].AreaName == null ? " " : countylist[t].AreaName);
                                 HSSFRow drow2 = (HSSFRow)sheet2.CreateRow(4 + t);
                                 drow2.CreateCell(0).SetCellValue(countylist[t].AreaName == null ? " " : countylist[t].AreaName);
                                 HSSFRow drow3 = (HSSFRow)sheet3.CreateRow(4 + t);
                                 drow3.CreateCell(0).SetCellValue(countylist[t].AreaName == null ? " " : countylist[t].AreaName);
                                 HSSFRow drow4 = (HSSFRow)sheet4.CreateRow(4 + t);
                                 drow4.CreateCell(0).SetCellValue(countylist[t].AreaName == null ? " " : countylist[t].AreaName);
                                 HSSFRow drow5 = (HSSFRow)sheet5.CreateRow(4 + t);
                                 drow5.CreateCell(0).SetCellValue(countylist[t].AreaName == null ? " " : countylist[t].AreaName);
                             }                       
                                 HSSFRow trow1 = (HSSFRow)sheet1.GetRow(4 + t);
                                 trow1.CreateCell(ss).SetCellValue(countylist[t].ZyArea == null ? "" : string.Format("{0:N1}", countylist[t].ZyArea));
                                 HSSFRow trow2 = (HSSFRow)sheet2.GetRow(4 + t);
                                 trow2.CreateCell(ss).SetCellValue(countylist[t].YshArea == null ? "" : string.Format("{0:N1}", countylist[t].YshArea));
                                 HSSFRow trow3 = (HSSFRow)sheet3.GetRow(4 + t);
                                 trow3.CreateCell(ss).SetCellValue(countylist[t].YshYield == null ? "" : string.Format("{0:N1}", countylist[t].YshYield));
                                 HSSFRow trow4 = (HSSFRow)sheet4.GetRow(4 + t);
                                 trow4.CreateCell(ss).SetCellValue(countylist[t].AvgPrice == null ? "" : string.Format("{0:N2}", countylist[t].AvgPrice));
                                 HSSFRow trow5 = (HSSFRow)sheet5.GetRow(4 + t);
                                 trow5.CreateCell(ss).SetCellValue(countylist[t].AddArea == null ? "" : string.Format("{0:N1}", countylist[t].AddArea));                                                                                                                                                                                                                                                                                                                                                                 
                             }
                         }*/
                }
            }
            
            using (MemoryStream ms =new MemoryStream())
            {
                workbook.Write(ms);           
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        public HSSFSheet CopyToSheet(HSSFSheet sheet, List<ProSeasonStatisModel> totallist,string DownType)
        {
            HSSFRow row = (HSSFRow)sheet.GetRow(3);
            ICellStyle style1 = sheet.Workbook.CreateCellStyle();
            ICellStyle style2 = sheet.Workbook.CreateCellStyle();
            if (totallist.Count > 0)
            {
                for (int i = 0; i < totallist.Count; i++)
                {
                    HSSFRow datarow = (HSSFRow)sheet.CreateRow(3 + i);
                    if (DownType.Equals("1"))
                    {
                        datarow.CreateCell(0).SetCellValue(totallist[i].Variety == null ? "" : totallist[i].Variety);
                    }
                    else
                    {
                        datarow.CreateCell(0).SetCellValue(totallist[i].AreaName == null ? "" : totallist[i].AreaName);
                    }                   
                    datarow.CreateCell(1).SetCellValue(totallist[i].ZyArea == null ? "" : string.Format("{0:N1}", totallist[i].ZyArea));
                    datarow.CreateCell(2).SetCellValue(totallist[i].YshArea == null ? "" : string.Format("{0:N1}", totallist[i].YshArea));
                    datarow.CreateCell(3).SetCellValue(totallist[i].YshYield == null ? "" : string.Format("{0:N1}", totallist[i].YshYield));
                    datarow.CreateCell(4).SetCellValue(totallist[i].AddArea == null ? "" : string.Format("{0:N1}", totallist[i].AddArea));
                    for (int j = 0; j <= 4; j++)
                    {
                        if (totallist[i].tag == 1)
                        {
                            style1.Alignment = row.GetCell(j).CellStyle.Alignment;
                            style1.FillPattern = FillPattern.SolidForeground;
                            style1.FillForegroundColor = HSSFColor.Yellow.Index;
                            datarow.GetCell(j).CellStyle = style1;
                        }
                        else if (totallist[i].tag == 2)
                        {
                            style2.Alignment = row.GetCell(j).CellStyle.Alignment;
                            style2.FillPattern = FillPattern.SolidForeground;
                            style2.FillForegroundColor = HSSFColor.Pink.Index;
                            datarow.GetCell(j).CellStyle = style2;
                        }
                        else
                        {
                            datarow.GetCell(j).CellStyle = row.GetCell(j).CellStyle;
                        }
                    }
                  
                }
            }
            return sheet;
        }

    }
}